The excel import template consists of different sheets:
Once a template has been fetched, the template can be populated with data either by manually entering data or by copying and pasting columns of data from other systems.
The Excel template has a few simple rules:
Some of the fields in the Excel document have a drop-down box where you can choose from valid enumerations for the given field. Other fields are filtered on maximum counts of letters or on numeric data types.
Retail items worksheet
To import items you need to create one new line per item to be created.
To edit items through excel change the settings of your exported items in this sheet and import again.
| Fields & buttons | Description |
|---|---|
| ITEMID | Alphanumerical characters are accepted. If the ID exists in the database that item will be updated depending on your import method selection in the Import Excel document dialog |
| DESCRIPTION | Enter the description of the item. This description is used on the receipts, item search and reports. When importing an item for the first time that is upon item creation this field must be filled in. |
| SEARCH ALIAS | An additional name for the item which is also searched through in the item search. Information is inserted into Retail > Retail items > Retail item view > Additional information tab > Search alias |
| NOTES | An additional text field to keep information about the item. Information is inserted into Retail > Retail items > Retail item view > Additional information tab > Extended description |
| INVENTORY UNIT | Enter the ID of the unit the item is ordered and stored in. This can be other than the sales unit but then make sure there exists a unit conversion between the two units. Information is inserted into Retail > Retail items > Retail item view > Inventory tab > Inventory unit  You can find the ID of the units in General setup > Units. Right click the unit line and select the Copy ID option and paste it into the Excel worksheet. |
| SALES UNIT | Enter the ID of the unit the item is sold in. This can be other than the inventory unit but then make sure there exists a unit conversion between the two units. Information is inserted into Retail > Retail items > Retail item view > Inventory tab > Inventory unit You can find the ID of the units in General setup > Units. Right click the unit line and select the Copy ID option and paste it into the Excel worksheet. |
| RETAIL GROUP | Enter the ID of the retail group the item should be added to. If you are creating new item groups also use the ID you are using for the new group in the Retail group worksheet. If the retail group already exists you can find the ID of the retail groups in Retail> Retail items > Retail groups. Right click the retail group line and select the Copy ID option and paste it into the Excel worksheet. |
| RETAIL DEPARTMENT | Enter the ID of the retail department the item should be added to. If you are creating new item departments also use the ID you are using for the new department in the retail department worksheet. If the retail group already exists you can find the ID of the retail groups in Retail> Retail items > Retail groups. Right click the retail group line and select the Copy ID option and paste it into the Excel worksheet. |
| SALES TAX GROUP |
Enter the ID of the item sales tax group the item should be in. You can find a list of all sales tax groups and their ID's in General setup > Sales tax > Item sales tax group. |
| COST PRICE | Enter the cost price of the item. The cost price is inserted into the field found at Retail item view > Prices tab > Cost price. |
| SALES PRICE |
Enter the cost price of the item. The cost price is inserted into the field found at Retail item view > Prices tab > Cost price. This price is used when the item is sold on the POS. |
| BARCODESETUP |
If you are importing bar codes for the item enter the ID of the barcode setup to be used. This field can be left empty even though you are importing bar codes. |
| BARCODE | Enter or scan in (if you have a keyboard scanner) the barcode to sell the item with. |
| BARCODEUNIT | Enter the ID of the Unit the item is sold in when the barcode is scanned. |
| SCALE ITEM |
Enter 0 if the item is NOT a scale item. Enter 1 if the item IS a scale item and should be weighed when sold. If the field is left empty the default is that the item is not a scale item. |
| KEY IN PRICE |
Select from the drop down list how keying in price for an item is handled on the POS. If this field is left empty the default is that keying in price is Not mandatory |
| KEY IN QUANTITY |
Select from the drop down list how keying in quantity for an item is handled on the POS. If this field is left empty the default is that keying in price is Not mandatory |
| ZERO PRICE VALID |
Enter 0 if zero price is NOT valid for the item . Enter 1 if zero price IS valid for the item. If the field is left empty the default is that zero price is NOT valid. |
| NO DISCOUNT ALLOWED |
Enter 0 if manual discounts can be given to this item. Enter 1 if NO manual discounts can be given to this item. If the field is left empty the default is that discounts are allowed. |
| MUST KEY IN COMMENT |
Enter 0 if a comment is not necessary. Enter 1 if the comment dialog should pop up every time this item is sold. If the field is left empty the comments will not be necessary. |
| QTY BECOMES NEGATIVE |
Enter 0 to sell the item in positive quantity. That is the item is subtracted from your inventory. Enter 1 to sell the item in negative quantity. The item is added to your inventory every time you sell it similar to when you return an item. If the field is left empty the item will be sold in positive quantity and subtracted from your inventory. |
| MUST SELECT UNIT |
Enter 0 if a unit selection is not necessary. Enter 1 if the unit of measure selection dialog should pop up every time this item is sold. If the field is left empty the unit selection will not be necessary. |
| VENDOR ITEMID | Enter the ID your vendor uses for this product. |
| VENTORID |
Enter the ID of the vendor you purchase this product from. If the vendor already exists in your data you can find the Vendor ID here: General setup > Inventory > View all vendors. If this is a new vendor you can import the vendor by filling in the Vendor |
| VENDOR PRICE | The price you buy the item for from the vendor. This value will be imported as last purchase price for this item and vendor. |
Retail groups worksheet
To import retail groups you need to create one new line per group to be created.
| Fields & buttons | Description |
|---|---|
| ID | Alphanumerical characters are accepted. If the ID exists in the database that retail group will be updated depending on your import method selection in the Import Excel document dialog. |
| DESCRIPTION | Enter the description of the retail group. This description is used on the receipts, item search and reports. When importing an retail group for the first time that is upon item creation this field must be filled in. |
| RETAIL DEPARTMENT ID |
Enter the ID of the department the retail group should be included in. This is not mandatory.
If the retail group already exists you can find the ID of the retail groups in Retail> Retail items > Retail groups. Right click the retail group line and select the Copy ID option and paste it into the Excel worksheet. |
| SALES TAX GROUP |
Enter the ID of the item sales tax group the items in the retail group should be in. All new items created in this retail group will automatically be set to this item sales tax group (but can be changed individually) You can find a list of all sales tax groups and their ID's in General setup > Sales tax > Item sales tax group. |
Retail department worksheet
To import retail departments you need to create one new line per department to be created.
| Fields & buttons | Description |
|---|---|
| ID | Alphanumerical characters are accepted. If the ID exists in the database that retail department will be updated depending on your import method selection in the Import Excel document dialog. |
| DESCRIPTION | Enter the description of the retail departments. This description is used on the receipts, item search and reports. When importing an retail department for the first time that is upon item creation this field must be filled in. |
Customers worksheet
To import customer you need to create one new line per customer to be created.
| Fields & buttons | Description |
|---|---|
| ID | Alphanumerical characters are accepted. If the ID exists in the database that customer will be updated depending on your import method selection in the Import Excel document dialog. |
| NAME |
Enter the name of the customer. This name is used on the receipts, item search and reports. If the whole name is entered into this field the name will be inserted to the Display name field in the Customer view. But if you also fill in one or more of the following fields, middlename, lastname, nameprefix, namesuffix then this NAME field serves as the first name field. |
| MIDDLENAME |
Enter the middle name of the customer. This name is used on the receipts, item search and reports. You can also leave this field empty. |
| LASTNAME |
Enter the last name of the customer. This name is used on the receipts, item search and reports. You can also leave this field empty. |
| NAMEPREFIX |
Use this field to add a name prefix such as Mr. or Mrs. to the customer name. You can also leave this field empty. |
| NAMESUFFIX |
Use this field to add a name suffix such as Jr. to the customer name. You can also leave this field empty. |
| ADDRESSLINE1 | Enter the default address of the customer. There are two lines to have enough space for a long address. |
| ADDRESSLINE2 | Enter the default address of the customer. There are two lines to have enough space for a long address. |
| CITY | Enter the city of the customer address. |
| STATE | Enter the state of the customer address. |
| ZIP | Enter the zip of the customer address. |
| COUNTRY | Enter the country of the customer address. |
| ADDRESS FORMAT |
Select of which address format this address is. If nothing is selected the default set in the Site Manager will be used. see further info in: |
| SEARCH ALIAS | Enter a search alias for the customer. This could be used for a ID number |
| PHONE | Enter the customer's phone number. |
| MOBILE PHONE | Enter the customer's mobile phone number. |
| EMAIL ADDRESS | Enter the customer's email address |
| LANGUAGE CODE | Enter the customer's language code. This is for informational use only. |
| RECEIPT EMAIL | Enter the email address the customer wants to receive the receipt to. A third party system is needed to actually send out the receipts. |
| RECEIPT OPTION |
Select how the customer wants to receive the receipt.
|
| ACCOUNT NUMBER | If another customer should be invoiced for this customers credit then enter the ID of the paying customer. |
| IDENTIFICATION NUMBER | This field can be used to keep an identification number of the customer. |
| CURRENCY | The default currency for this customer. |
| VAT NUMBER |
Enter the customer's VAT number if any. |
| SALES TAX GROUP | Enter the ID of the Customer sales tax group this customer should belong to. If nothing is selected the store sales tax group is used for tax calculations. This also depends on the tax calculation settings set on the Store. |
| MAXIMUM WITHDRAWAL | Enter the maximum credit limit this customer should have. If this customer is allowed to charge to his account then you have to enter |
| BLOCKING |
Select one of the following option:
|
| CASH CUSTOMER |
Enter 0 if a comment is not necessary. Enter 1 if the comment dialog should pop up every time this item is sold. If the field is left empty the comments will not be necessary. |
Vendor worksheet
To import vendors you need to create one new line per vendor to be created.
| Fields & buttons | Description |
|---|---|
| VENDORID |
Enter an ID for the Vendor. Alphanumerical characters are accepted. If the ID exists in the database that vendor will be updated depending on your import method selection in the Import Excel document dialog. |
| DESCRIPTION |
Enter the name of the vendor. This name is used on the receipts, item search and reports. |
| ADDRESSLINE1 | Enter the default address of the customer. There are two lines to have enough space for a long address. |
| ADDRESSLINE2 | Enter the default address of the customer. There are two lines to have enough space for a long address. |
| CITY | Enter the city of the customer address. |
| STATE | Enter the state of the customer address. |
| ZIP | Enter the zip of the customer address. |
| COUNTRY | Enter the country of the customer address. |
| ADDRESS FORMAT |
Select of which address format this address is. If nothing is selected the default set in the Site Manager will be used. see further info in: |
| CURRENCY | Enter the ID of the currency used when doing business with this vendor. |
| PHONE | Enter the phone number of the vendor. |
| EMAIL ADDRESS | Enter the vendor's email address. |
| FAX | Enter the vendor's fax number. |
| LANGUAGE CODE | Enter the vendors's language code. This is for informational use only. |
| NOTES | Enter any notes you might have for this vendor. |
| TAX GROUP | Enter the ID of the sales tax code used for this vendor. |
| CONTACT TYPE | Select which type of contact the following information is for, a company or a person. |
| CONTACT COMPANYNAME | Enter the name of the whole name of the company here. IF this is a person you can either enter the whole name here or skip this field and fill the other name fields. |
| CONTACT NAMEPREFIX |
Use this field to add a name prefix such as Mr. or Mrs. to the contact name. You can also leave this field empty. |
| CONTACT FIRSTNAME | Enter the first name of the contact. |
| CONTACT MIDDLENAME |
Enter the middle name of the contact. |
| CONTACT LASTNAME |
Enter the last name of the contact. |
| CONTACT NAMESUFFIX |
Use this field to add a name suffix such as Jr. to the contact name. |
| CONTACT ADDRESSLINE1 | Enter the default address of the contact. There are two lines to have enough space for a long address. |
| CONTACT ADDRESSLINE2 | Enter the default address of the contact. There are two lines to have enough space for a long address. |
| CONTACT CITY | Enter the city of the contact address. |
| CONTACT STATE | Enter the state of the contact address. |
| CONTACT ZIP | Enter the zip of the contact address. |
| CONTACT COUNTRY | Enter the country of the contact address. |
| CONTACT ADDRESS FORMAT |
Select of which address format this address is. If nothing is selected the default set in the Site Manager will be used. see further info in: |
| CONTACT PHONE | Enter a phone number for the vendor contact. |
| CONTACT PHONE2 | Enter another phone number for the vendor contact. |
| CONTACT FAX | Enter a fax number for the vendor tasks. |
_LookupValues worksheet
This worksheet only includes lookup information for the other worksheets and should not be edited.
Copyright © 2015 LS Retail ehf. All rights reserved.
